﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using eCommerce.db.DataRespository;
using eCommerce.db.SQLDataAccess;

namespace eCommerce.db.DALRespository
{
    public class CustomerDal
    {
        #region CustomerDal private
        private static void SetInfo(IDataRecord reader, ref CustomerEntity o)
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                PropertyInfo propertyInfo = DbCommon.GetPropertyInfo(o, reader.GetName(i));
                if (((propertyInfo != null) && (reader[i] != null)) && (reader[i] != DBNull.Value))
                    propertyInfo.SetValue(o, reader[i], null);
                else
                    o[reader.GetName(i)] = reader[i];
            }
        }

        private static void SetInfoList(ref SqlDataReader reader, ref List<CustomerEntity> customerList)
        {
            while (reader.Read())
            {
                var o = new CustomerEntity();
                SetInfo(reader, ref o);
                customerList.Add(o);
            }
            reader.Dispose();
        }        
        #endregion

        public static List<CustomerEntity> SearchBuyAndSell(string keyword, int storeId, bool isExactly, bool isAdministrator, int pageIndex, int pageSize, ref int totalRecords)
        {
            var filter = "1=1";

            if (!string.IsNullOrEmpty(keyword))
                filter += string.Format(" AND (ContactName LIKE N'%{0}%' OR Mobile LIKE N'%{0}%')", keyword);

            if (!string.IsNullOrEmpty(keyword) && isExactly)
                filter += string.Format(" AND (Mobile = N'%{0}%')", keyword);

            if (isAdministrator == false)
                if (storeId > 0)
                    filter += string.Format(" AND StoreId = {0}", storeId);        

            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var lstCustomer = new List<CustomerEntity>();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Action",
                                Constant.TableCustomer, "CustomerId DESC", Constant.CustomerFieldList, filter, pageIndex, pageSize);

                SetInfoList(ref reader, ref lstCustomer);

                totalRecords = (int)SqlHelper.ExecuteScalar(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Action_Count",
                                        Constant.TableCustomer, filter);

                return lstCustomer;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Customer_Action_Pagination: {0}", ex.Message));
            }                  
        }

        public static List<CustomerEntity> Search(string keyword, int storeId, int status, int orderBy, int pageIndex, int pageSize, ref int totalRecords)
        {
            var filter = "1=1";

            if (!string.IsNullOrEmpty(keyword))
                filter += string.Format(" AND (ContactName LIKE N'%{0}%' OR Mobile LIKE N'%{0}%')", keyword); // OR Email LIKE N'%{0}%'

            if (storeId > 0)
                filter += string.Format(" AND StoreId = {0}", storeId);

            if (status > 0)
                filter += string.Format(" AND [IsActive] = {0}", status);

            string sortBy;

            switch (orderBy)
            {
                case 1:
                    sortBy = "CustomerId DESC";
                    break;
                case 2:
                    sortBy = "CustomerId ASC";
                    break;
                case 3:
                    sortBy = "ContactName ASC";
                    break;
                case 4:
                    sortBy = "ContactName DESC";
                    break;
                default:
                    sortBy = "CustomerId DESC";
                    break;
            }

            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var lstCustomer = new List<CustomerEntity>();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Action",
                                Constant.TableCustomer, sortBy, Constant.CustomerFieldList, filter, pageIndex, pageSize);

                SetInfoList(ref reader, ref lstCustomer);

                totalRecords = (int) SqlHelper.ExecuteScalar(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Action_Count", 
                                        Constant.TableCustomer, filter);

                return lstCustomer;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Customer_Action_Pagination: {0}", ex.Message));
            }                     
        }                

        public static CustomerEntity GetById(int id)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var customer = new CustomerEntity();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Customer_GetById", id);
                if (reader.Read())
                    SetInfo(reader, ref customer);
                return customer;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Customer_GetById: {0}", ex.Message));
            }
        }

        public static CustomerEntity GetByMobile(string mobile)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var customer = new CustomerEntity();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Customer_GetByMobile", mobile);
                if (reader.Read())
                    SetInfo(reader, ref customer);
                return customer;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Customer_GetByMobile: {0}", ex.Message));
            }
        }

        public static void ChangeStatus(int id, int isActive)
        {
            try
            {
                if (id > 0)
                    SqlHelper.ExecuteNonQuery(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Customer_Update_Status", id, isActive);
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Customer_Update_Status: {0}", ex.Message));
            }
        }        

        public static int Save(CustomerEntity o)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            try
            {
                if (o.CustomerId > 0)
                {
                    SqlHelper.ExecuteNonQuery(
                    DbCommon.ConnectionString,
                    DbCommon.DatabaseSchema + "Customer_Update", o.CustomerId, o.ContactName, o.Mobile, o.Email, o.Address, o.Description);
                    return o.CustomerId;
                }
                else
                {
                    return (int) SqlHelper.ExecuteScalar(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Customer_Create",
                                        o.StoreId, o.ContactName, o.Mobile, o.Email, o.Address, o.Description, o.IsActive);    
                }
                
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Customer Save: {0}", ex.Message));
            }
        }

        public static void Delete(int id)
        {
            try
            {
                if (id > 0)
                    SqlHelper.ExecuteNonQuery(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Customer_Delete", id);
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Customer Delete: {0}", ex.Message));
            }
        }
    }
}
